﻿<html DIR="LTR" xmlns:tool="http://www.microsoft.com/tooltip" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:ddue="http://ddue.schemas.microsoft.com/authoring/2003/5" xmlns:MSHelp="http://msdn.microsoft.com/mshelp">
  <head>
    <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=utf-8" />
    <META NAME="save" CONTENT="history" />
    <title>Readme_Lookup Transformation Sample</title>
    
    
    
    <style TYPE="text/css">
body
{
background: #FFFFFF;
color: #000000;
font-family:    Verdana;
font-size: medium;
font-style: normal;
font-weight: normal;
margin-top: 0;
margin-bottom:  0;
margin-left:    0;
margin-right:   0;
width:  100%;
}

div.#mainSection
{
font-size: 70%;
width: 100%;
padding-left:    10;
margin-right: 10;
}

div.#mainBody
{
font-size: 90%;
margin-top: 10;
padding-bottom: 20;
}

div.#header
{
background-color: #D2D2D2;
padding-top:    0;
padding-bottom: 0;
padding-left:   10;
padding-right:  0;
width:          100%;
}

div.#header table
{
border-bottom-color: #C8CDDE;
border-bottom-style: solid;
border-bottom-width: 1;
width:  100%;
}

span.#runningHeaderText
{
color: #003399;
font-size: 90%;
}

span.#nsrTitle
{
/*    color: #003399;*/
font-size: 120%;
font-weight: 600;
}

div.#header table td
{
color: #000000;
font-size: 70%;
margin-top: 0;
margin-bottom:  0;
padding-right: 20;
}

div.#header table tr.#headerTableRow3 td
{
padding-bottom: 2;
padding-top: 5;
}

div.#header table.#bottomTable
{
border-top-color: #FFFFFF;
border-top-style: solid;
border-top-width: 1;
text-align: left;
}

div.#footer
{
font-size: 90%;
margin-top: 0;
margin-bottom:  0;
margin-left:    -5;
margin-right:   0;
padding-top:    2;
padding-bottom: 2;
padding-left:   0;
padding-right:  0;
width:  100%;
}

hr.#footerHR
{
border-bottom-color: #EEEEFF;
border-bottom-style: solid;
border-bottom-width: 1;
border-top-color: C8CDDE;
border-top-style: solid;
border-top-width: 1;
height: 3;
color: #D2D2D2;
}

div.section
{
padding-top:    2;
padding-bottom: 2;
padding-right:  15;
width:  100%;
}

.heading
{
color:          #000000;
font-weight:    bold;
margin-top:     18;
margin-bottom:  8;
}

h1.heading
{
color: #000000;
font-size:  150%;
}

.subHeading
{
color:          #000000;
font-weight:    bold;
font-size:      150%;
margin-bottom:  4;
}

h2.subHeading
{
color:          #000000;
font-weight:    bold;
font-size:      130%;
}
h3.subHeading
{
color:  #000000;
font-size: 125%;
font-weight: bold;
}

h4.subHeading
{
color: #000000;
font-size: 110%;
font-weight: bold;
}

h4.procedureHeading
{
color: #000080;
font-size: 110%;
font-weight: bold;
}

h5.subHeading
{
color: #000000;
font-size: 100%;
font-weight: bold;
}

img
{
padding-bottom: 10;
}

img.toggle
{
border: 0;
margin-right: 5;
padding-bottom: 10;
}

img.copyCodeImage
{
border: 0;
margin: 1;
margin-right: 3;
padding-bottom: 10;
}

img.downloadCodeImage
{
border: 0;
margin-right: 3;
padding-bottom: 10;
}

img.viewCodeImage
{
border: 0;
margin-right: 3;
padding-bottom: 10;
}

img.note
{
border: 0;
margin-right: 3;
padding-bottom: 10;
}

img.#membersOptionsFilterImage
{
border: 0;
margin-left: 10;
vertical-align: middle;
padding-bottom: 10;
}

img.#toggleAllImage
{
margin-left: 4;
vertical-align: middle;
padding-bottom: 10;
}

div.#mainSection table
{
border: 0;
font-size: 100%;
width:  100%;
margin-top: 5px;
margin-bottom: 15px;
}

div.#mainSection table tr
{
vertical-align: top;
}

div.#mainSection table th
{
text-align: left;
background: #D8D8D8;
border-bottom-color: #D8D8D8;
border-bottom-style: solid;
border-bottom-width: 1;
color: #000000;
padding-left: 5;
padding-right: 5;
}

div.#mainSection table td
{
background: #F2F2F2;
border-top-color: #D8D8D8;
border-top-style: solid;
border-top-width: 1;
padding-left: 5;
padding-right: 5;
}

div.#mainSection table td.imageCell
{
white-space: nowrap;
}

div.code
{
width: 98%;
}

div.code table
{
border: 0;
font-size: 95%;
margin-bottom: 5;
width: 100%
}

div.code table th
{
text-align: left;
background: #D8D8D8;
border-bottom-color: #D8D8D8;
border-bottom-style: solid;
border-bottom-width: 1;
color: #000000;
font-weight: bold;
padding-left: 5;
padding-right: 5;
}

div.code table td
{
background: #CCCCCC;
border-top-color: #D8D8D8;
border-top-style: solid;
border-top-width: 1;
padding-left: 5;
padding-right: 5;
padding-top: 5;
}

div.alert
{
margin-left: 10;
width: 98%;
}

div.alert table
{
border: 1;
font-size: 100%;
width:  100%;
border: solid 1 #DEDFEF;
}

div.alert table th
{
text-align: left;
background: #D8D8D8;
border-bottom-width: 0;
color: #000000;
padding-left: 5;
padding-right: 5;
border: solid 1 #DEDFEF;
}

div.alert table td
{
background: #FFFFFF;
border-top-color: #D8D8D8;
border-top-style: solid;
border-top-width: 1;
padding-left: 5;
padding-right: 5;
border: solid 1 #DEDFEF;
}

span.copyCode
{
color: #0000ff;
font-size: 90%;
font-weight: normal;
cursor: hand;
float: right;
display: inline;
text-align: right;
}

.downloadCode
{
color: #0000ff;
font-size: 90%;
font-weight: normal;
cursor: hand;
}

.viewCode
{
color: #0000ff;
font-size: 90%;
font-weight: normal;
cursor: hand;
}

div.code pre
{
font-family:    Monospace, Courier New, Courier;
font-size: 105%;
color:  #000000;
}

code
{
font-family:    Monospace, Courier New, Courier;
font-size: 105%;
color:  #000000;
}

dl
{
margin-top: 0;
padding-left:   1;
}

dd
{
margin-bottom:  0;
margin-left:    0;
padding-left:   20;
}

dd p
{
margin-top: 5;
}

ul
{
margin-left: 17;
list-style-type: disc;
}

ul ul
{
margin-bottom: 4;
margin-left: 17;
margin-top: 3;
list-style-type: disc;
}

ol
{
margin-left: 24;
list-style-type: decimal;
}

ol ol
{
margin-left: 24;
margin-top: 3;
list-style-type: lower-alpha;
}

li
{
margin-top: 0;
margin-bottom: 0;
padding-bottom: 0;
padding-top: 0;
margin-left: 5;
}

p
{
margin-bottom: 15;
}

.tip
{
color:  #0000FF;
font-style: italic;
cursor:hand;
text-decoration:underline;
}

.math
{
font-family: Times New Roman;
font-size: 125%
}
.sourceCodeList
{
font-family: Verdana;
font-size: 90%;
}

pre.viewCode
{
width: 100%;
overflow: auto;
}

li:hover table, li.over table
{
background-color: #C0C0C0;
}

li:hover ul, li.over ul
{
background-color: #d2d2d2;
border: 1px solid #000;
display: block;
}
            </style>
  </head>
  <body>
    <!--Topic built:06/04/2008 01:31:57-->

    
    
    
    
    
    
    
    
    
    <div id="header">
      <table width="100%" id="topTable"><tr>
          <td align="left">
            <span id="nsrTitle">Readme_Lookup Transformation Sample</span>
          </td>
          <td align="right">
            <span id="headfb" class="feedbackhead">
            </span>
          </td>
        </tr></table>
      
      
      
    </div>
    <div id="mainSection">
      <div id="mainBody"><p> 6/4/2008 1:31:57 AM</p>
        
        <font color="DarkGray"> </font><p /> 
        <span id="changeHistory">
        </span>
    <p>
      This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
    </p>
    <p>The Lookup Transformation sample is a sample Integration Services package that implements the Lookup transformation in full cache mode using the Cache connection manager. The transformation performs lookups on a reference dataset that is stored in a text file.</p>
    <p>The two data flows in the package demonstrate the following tasks:</p>
    <ul><li>
        The first data flow loads a reference dataset from a text file, and uses a Cache Transform transformation to populate a Cache connection manager with the data.<br />
      </li><li>
        The second data flow loads a data source from a text file. The data flow then uses the Lookup transformation to perform lookups by joining data in the input columns with columns in the reference dataset. The Lookup transformation uses the Cache connection manager to access the reference dataset and cache the reference dataset before the transformation runs.<br />
      </li><li>
        The second data flow also redirects data from the Lookup transformation to different Flat File destinations. These different destinations correspond to rows with matching entries in the reference dataset, rows without matching entries, and rows with truncation errors. To count the rows directed to each Flat File destination and store this count information in user-defined variables, the second data flow uses a Row Count transformation.<br />
      </li></ul>
    <p>If you run the sample on a non-English version of Windows, you might have to substitute the localized name of the Program Files folder to open or run the sample.</p>
    <div class="alert"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left">Important: </th></tr><tr><td>
      Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.<p />
    </td></tr></table><p /></div>
  <h1 class="heading">Requirements</h1><div id="requirementsSection" class="section">
    <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">To run this sample package, the following components are required:</p>
      <ul xmlns=""><li>
          The sample package and data files that the package uses must be installed on the local hard disk drive. <br />
        </li><li>
          If you run the sample package only from the command line, you must install Integration Services. <br />
        </li><li>
          If you open the package in SSIS Designer and run the sample package, you must install Business Intelligence Development Studio.<br />
        </li></ul>
      <p xmlns="">For more information about how to install samples, see "Installing Sample Integration Services Packages" in SQL Server Books Online.</p>
    </content>
  </div><h1 class="heading">Location of the Sample Package</h1><div id="sectionSection0" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">If you installed the samples to the default installation location, the Lookup sample package is located in the following folder: </p>
      <p xmlns="">
        C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\Lookup Sample\</p>
      <p xmlns="">To run this sample package, the files listed in the following table must be stored on the computer that is running the package.</p>
      <table width="100%" cellspacing="0" cellpadding="0" border="1" style="background-color: #CCCCCC;" xmlns=""><tr>
            <th>
              File
            </th>
            <th>
              Description
            </th>
          </tr><tr>
          <td>
            <p>LookupSample.dtsx</p>
          </td>
          <td>
            <p>The sample package.</p>
          </td>
        </tr><tr>
          <td>
            <p>DimTime.csv</p>
          </td>
          <td>
            <p>The reference dataset.</p>
          </td>
        </tr><tr>
          <td>
            <p>FactBudget.csv</p>
          </td>
          <td>
            <p>The input dataset.</p>
          </td>
        </tr><tr>
          <td>
            <p>MatchOutput.csv</p>
          </td>
          <td>
            <p>The text file to which the sample writes rows that have matching entries in the reference dataset.</p>
          </td>
        </tr><tr>
          <td>
            <p>NoMatchOutput.csv</p>
          </td>
          <td>
            <p>The text file to which the sample writes rows that do not have matching entries in the reference dataset.</p>
          </td>
        </tr><tr>
          <td>
            <p>ErrorOutput.csv</p>
          </td>
          <td>
            <p>The text file to which the sample writes truncation errors.</p>
          </td>
        </tr></table>
    </content></div><h1 class="heading">Running the Sample</h1><div id="sectionSection1" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">The package can be run from the command line by using the <b>dtexec</b> utility (dtexec.exe), or can be run in Business Intelligence Development Studio.</p>
      <p xmlns="">If you are using a non-English version of Windows, you might have to update the <b>ConnectionString</b> property of any file connection managers that the package uses. Otherwise, the sample package might not run successfully. </p>
      <p xmlns="">Before running the package, verify that the path that the connection manager uses is valid on your computer. If you have to, modify the path that the connection manager uses so that the path uses the localized name of the Program Files folder.</p>
      <p xmlns="">For this sample, you might have to update "Program Files" in the <b>ConnectionString</b> property for the Create_Execute_Process_Dest and customers connection managers.</p>
      <h4 class="procedureHeading" xmlns="">To run the package by using dtexec</h4><div id="procedureSectionEFBHBHA" class="section" xmlns=""><ol><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Open a Command Prompt window.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Change the directory to C:\Program Files\Microsoft SQL Server\100\DTS\Binn.</p>
              <p xmlns="">This directory contains the <b>dtexec</b> utility.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Type the following command:</p>
              <div class="code" xmlns=""><span codeLanguage="other"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left" /></tr><tr><td colspan="2"><pre>dtexec /f "C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\Lookup Sample\LookupSample\LookupSample.dtsx"</pre></td></tr></table></span></div>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Press ENTER.</p>
            </content>
          </li></ol></div>
      <p xmlns="">For more information about how to run the package by using the <b>dtexec</b> utility, see the topic, "dtexec Utility", in SQL Server Books Online.</p>
      <h4 class="procedureHeading" xmlns="">To run the package in Business Intelligence Development Studio</h4><div id="procedureSectionEBBHBHA" class="section" xmlns=""><ol><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Open Business Intelligence Development Studio.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Create a new project and a new Integration Services solution.</p>
              <p xmlns="">The new solution is added to <b>Solution Explorer</b>, and the new project is added to the solution. For more information, see <b>How to: Create a New Integration Services Project</b>.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">In <b>Solution Explorer</b>, right-click the <b>SSIS Packages</b> folder, and then click <b>Add Existing Package</b>.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Add the LookupSample.dtsx package by doing the following steps:</p>
              <ol xmlns=""><li>
                  In the <b>Package location</b> list, select <b>File System</b>.<br />
                </li><li>
                  Click the browse button <b>(...)</b>, select the LookupSample.dtsx package, and then click <b>OK</b>.<br />
                </li></ol>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">In <b>Solution Explorer</b>, in the <b>SSIS Packages</b> folder, right-click LookupSampl.dtsx, and then click <b>Execute Package</b>.</p>
            </content>
          </li></ol></div>
    </content></div><h1 class="heading">Components in the Sample</h1><div id="sectionSection2" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">The following table lists the Integration Services tasks and connection managers that the sample uses.</p>
      <table width="100%" cellspacing="0" cellpadding="0" border="1" style="background-color: #CCCCCC;" xmlns=""><tr>
            <th>
              Element
            </th>
            <th>
              Purpose
            </th>
          </tr><tr>
          <td>
            <p>Data Flow task</p>
          </td>
          <td>
            <p>The first DataFlow task, <b>DFT Load Lookup Cache</b>, loads the reference dataset from the DimTime.csv text file and uses a Cache Transform transformation to populate a Cache connection manager with the data. </p>
          </td>
        </tr><tr>
          <td>
            <p>Derived Column transformation</p>
          </td>
          <td>
            <p>The Derived Column transformation modifies the data before the Cache Transform transformation writes the data to the Cache connection manager. The Derived Column transformation applies expressions to existing columns to create two new columns.</p>
          </td>
        </tr><tr>
          <td>
            <p>Cache Transform transformation</p>
          </td>
          <td>
            <p>The Cache Transform transformation maps columns from the DimTim.csv text file and Derived Column transformation to columns in the Cache connection manager.</p>
          </td>
        </tr><tr>
          <td>
            <p>Data Flow task</p>
          </td>
          <td>
            <p>The second DataFlow task, <b>DFT Load Fact Table</b>, loads data from the FactBudget.csv text file. The task uses a Lookup transformation to perform lookups by joining data in the input columns with columns in the reference dataset.</p>
          </td>
        </tr><tr>
          <td>
            <p>Lookup transformation</p>
          </td>
          <td>
            <p>The Lookup transformation, <b>Lookup DimTime</b>, uses a Cache connection manager to access the reference dataset and to cache this dataset before the Lookup transformation runs.</p>
            <p>The Lookup transformation also directs rows to three Flat File destinations:</p>
            <ul><li>
                Rows with matching entries in the reference dataset are directed to the match output.<br />
              </li><li>
                Rows without matching entries are directed to the no match output.<br />
              </li><li>
                Rows with truncation errors are directed to the error output.<br />
              </li></ul>
          </td>
        </tr><tr>
          <td>
            <p>Row Count transformation</p>
          </td>
          <td>
            <p>The Row Count transformation counts the number of rows directed to each Flat File destination and stores the information in user-defined variables.</p>
          </td>
        </tr><tr>
          <td>
            <p>Cache connection manager</p>
          </td>
          <td>
            <p>The Cache connection manager stores, in-memory, the data that both data flows use.</p>
          </td>
        </tr><tr>
          <td>
            <p>Flat File destination</p>
          </td>
          <td>
            <p>The output from the Lookup transformation is stored in Flat File destinations.</p>
          </td>
        </tr><tr>
          <td>
            <p>Flat File connection managers</p>
          </td>
          <td>
            <p>The five flat file connection managers connect to the input and output text files.</p>
          </td>
        </tr></table>
    </content></div><h1 class="heading">Sample Results</h1><div id="sectionSection3" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">The execution results of this sample package are saved to three flat files, which are located in the following folder:</p>
      <p xmlns="">
        C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\Lookup Sample\DataFiles</p>
    </content></div><!--[if gte IE 5]>
			<tool:tip element="seeAlsoToolTip" avoidmouse="false"/><tool:tip element="languageFilterToolTip" avoidmouse="false"/><tool:tip element="roleInfoSpan" avoidmouse="false"/>
		<![endif]--></div>
      <div id="footer">
			
			© 2008 Microsoft Corporation. All rights reserved.
		</div>
    </div>
  </body>
</html>